/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package BookTransaction;

import Admission.AdmissionBean;
import Book.BookBean;
import Book.BookDetailsBean;
import java.sql.*;
import java.util.Vector;

/**
 *
 * @author mishra
 */
public class Query {

    static Connection con = null;

    public static Date addDays(Date d, int days) {
        d.setTime(d.getTime() + days * 1000 * 60 * 60 * 24);
        return d;
    }

    public static int bookIssue(BookTransBean btb) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("insert into BOOK_TRANSACTION values(BOOKTRANSACTIONID.nextval,?,?,?,?,?,?,?,?,sysdate)");
            pre.setInt(1, btb.getUserType());
            pre.setString(2, btb.getEmployeeAddNo());
            pre.setDate(3, btb.getIssueDate());
            pre.setDate(4, btb.getRetruningDate());
            pre.setInt(5, btb.getFine());
            pre.setInt(6, btb.getBookStatus());
            pre.setString(7, btb.getBookNo());
            pre.setString(8, btb.getUserEmail());
            i = pre.executeUpdate();
            if (i != 0) {
                PreparedStatement pre1 = con.prepareStatement("update book_details set book_status=1 where book_number=?");
                pre1.setString(1, btb.getBookNo());
                i = pre1.executeUpdate();
            }

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }

    public static int viewDetailStatus(String id) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select BOOK_STATUS from BOOK_DETAILS where BOOK_NUMBER=?");
            pre.setString(1, id);
            ResultSet rs = pre.executeQuery();
            if (rs.next()) {
                i = rs.getInt(1);
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }

    public static BookTransBean viewRetDetail(String id) throws SQLException {
        int i = 0;
        BookTransBean btb = new BookTransBean();
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select BOOK_TRANSACTION_ID, USER_TYPE, EMPLOYEE_ADD_NO, issue_date, RETURNING_DATE, FINE, BOOK_STATUS, BOOK_NO, USER_EMAIL, ENTRY_DATE, (to_date(sysdate)-to_date(issue_date))total_days, (to_date(sysdate)-to_date(RETURNING_DATE))late_days from BOOK_TRANSACTION where book_no=? AND book_status=1");
            pre.setString(1, id);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                btb.setBookTransactionId(rs.getInt(1));
                btb.setUserType(rs.getInt(2));
                btb.setEmployeeAddNo(rs.getString(3));
                btb.setIssueDate(rs.getDate(4));
                btb.setRetruningDate(rs.getDate(5));
                btb.setFine(rs.getInt(6));
                btb.setBookStatus(rs.getInt(7));
                btb.setBookNo(rs.getString(8));
                btb.setUserEmail(rs.getString(9));
                btb.setEntryDate(rs.getDate(10));
                btb.setTotalDays(rs.getInt(11));
                btb.setLateDays(rs.getInt(12));
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return btb;
    }

    public static int bookReturn(BookTransBean btb, BookDetailsBean bb) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("insert into BOOK_TRANSACTION values(BOOKTRANSACTIONID.nextval,?,?,?,sysdate,?,?,?,?,sysdate)");
            pre.setInt(1, btb.getUserType());
            pre.setString(2, btb.getEmployeeAddNo());
            pre.setDate(3, btb.getIssueDate());
            pre.setInt(4, btb.getFine());
            pre.setInt(5, btb.getBookStatus());
            pre.setString(6, btb.getBookNo());
            pre.setString(7, btb.getUserEmail());
            i = pre.executeUpdate();
            if (i != 0) {
                PreparedStatement pre1 = con.prepareStatement("update BOOK_DETAILS set book_status=?, BOOK_STATUS_DESCRIPTION=?, ENTRY_DATE=sysdate where BOOK_NUMBER=?");
                pre1.setInt(1, bb.getBookStatus());
                pre1.setString(2, bb.getBookStatusDescription());
                pre1.setString(3, bb.getBookNumber());
                i = pre1.executeUpdate();
            }

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }

    public static int viewBookStatus(String id) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select BOOK_STATUS from BOOK_DETAILS where BOOK_NUMBER=?");
            pre.setString(1, id);
            ResultSet rs = pre.executeQuery();
            if (rs.next()) {
                i = rs.getInt(1);
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }

    public static Vector viewMovementLog() throws SQLException {
        Vector<Object> list = new Vector<Object>();
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from book_transaction left join book_details on book_transaction.book_no=book_details.book_number left join book on book_details.book_id=book.book_id");

            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                BookTransBean btb = new BookTransBean();
                BookDetailsBean bdb = new BookDetailsBean();
                BookBean bb = new BookBean();
                btb.setBookTransactionId(rs.getInt(1));
                btb.setUserType(rs.getInt(2));
                btb.setEmployeeAddNo(rs.getString(3));
                btb.setIssueDate(rs.getDate(4));
                btb.setRetruningDate(rs.getDate(5));
                btb.setFine(rs.getInt(6));
                btb.setBookStatus(rs.getInt(7));
                btb.setBookNo(rs.getString(8));
                btb.setUserEmail(rs.getString(9));
                btb.setEntryDate(rs.getDate(10));
                bdb.setBookDetailsId(rs.getInt(11));
                bdb.setBookId(rs.getInt(12));
                bdb.setBookNumber(rs.getString(13));
                bdb.setBookStatus(rs.getInt(14));
                bdb.setBookStatusDescription(rs.getString(15));
                bdb.setEntryDate(rs.getDate(16));
                bb.setBookId(rs.getInt(17));
                bb.setBookCategoryId(rs.getInt(18));
                bb.setBookTypeId(rs.getInt(19));
                bb.setBookName(rs.getString(20));
                bb.setPublisher(rs.getString(21));
                bb.setAuthor(rs.getString(22));
                bb.setYear(rs.getString(23));
                bb.setQuantity(rs.getInt(24));
                bb.setPrice(rs.getFloat(25));
                bb.setFine(rs.getInt(26));
                bb.setGracePeriod(rs.getInt(27));
                bb.setEntryEmail(rs.getString(28));
                bb.setIsbn(rs.getString(29));
                bb.setBookDescription(rs.getString(30));
                bb.setEntryDate(rs.getDate(31));
                btb.setBookDetailsBean(bdb);
                btb.setBookBean(bb);
                list.add(btb);

            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;
    }

    public static Vector viewMovementLogAStu(String email) throws SQLException {
        Vector<Object> list = new Vector<Object>();
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from admission a left join book_transaction bt on bt.EMPLOYEE_ADD_NO=a.admission_id  left join book_details bd on bt.book_no=bd.book_number left join book b on bd.book_id=b.book_id where a.STUDENT_EMAIL=? and bt.USER_TYPE=2");
            pre.setString(1, email);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                AdmissionBean ab = new AdmissionBean();
                BookTransBean btb = new BookTransBean();
                BookDetailsBean bdb = new BookDetailsBean();
                BookBean bb = new BookBean();

                ab.setAddmissionId(rs.getInt(1));
                ab.setStudentEmail(rs.getString(2));
                ab.setStudentMobile(rs.getString(3));
                ab.setRollNumber(rs.getString(4));
                ab.setStudentSession(rs.getString(5));
                ab.setCourseId(rs.getInt(6));
                ab.setApplicationNo(rs.getString(7));
                ab.setAdmissionDate(rs.getDate(8));
                ab.setEntryDate(rs.getDate(9));
                ab.setEntryEmail(rs.getString(10));
                ab.setSemester(rs.getInt(11));

                btb.setBookTransactionId(rs.getInt(12));
                btb.setUserType(rs.getInt(13));
                btb.setEmployeeAddNo(rs.getString(14));
                btb.setIssueDate(rs.getDate(15));
                btb.setRetruningDate(rs.getDate(16));
                btb.setFine(rs.getInt(17));
                btb.setBookStatus(rs.getInt(18));
                btb.setBookNo(rs.getString(19));
                btb.setUserEmail(rs.getString(20));
                btb.setEntryDate(rs.getDate(21));

                bdb.setBookDetailsId(rs.getInt(22));
                bdb.setBookId(rs.getInt(23));
                bdb.setBookNumber(rs.getString(24));
                bdb.setBookStatus(rs.getInt(25));
                bdb.setBookStatusDescription(rs.getString(26));
                bdb.setEntryDate(rs.getDate(27));

                btb.setBookDetailsBean(bdb);

                bb.setBookId(rs.getInt(28));
                bb.setBookCategoryId(rs.getInt(28));
                bb.setBookTypeId(rs.getInt(30));
                bb.setBookName(rs.getString(31));
                bb.setPublisher(rs.getString(32));
                bb.setAuthor(rs.getString(33));
                bb.setYear(rs.getString(34));
                bb.setQuantity(rs.getInt(35));
                bb.setPrice(rs.getFloat(36));
                bb.setFine(rs.getInt(37));
                bb.setGracePeriod(rs.getInt(38));
                bb.setEntryEmail(rs.getString(39));
                bb.setIsbn(rs.getString(40));
                bb.setBookDescription(rs.getString(41));
                bb.setEntryDate(rs.getDate(42));

                btb.setBookBean(bb);
                ab.setBookTranBean(btb);
                list.add(ab);
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;
    }

    public static Vector getReminder(int days) throws SQLException {
        Vector<BookTransBean> list = new Vector<BookTransBean>();
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from book_transaction bt left join (select book_number,book_id from BOOK_DETAILS) bd on bt.book_no=bd.book_number left join (select book_id,book_name from book) b on bd.book_id=b.book_id where bt.returning_date < to_date(sysdate+" + days + ")");
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                BookTransBean btb = new BookTransBean();
                BookBean bb = new BookBean();
                btb.setUserType(rs.getInt("USER_TYPE"));
                btb.setIssueDate(rs.getDate("ISSUE_DATE"));
                btb.setRetruningDate(rs.getDate("RETURNING_DATE"));
                btb.setBookNo(rs.getString("BOOK_NO"));
                bb.setBookName(rs.getString("BOOK_NAME"));
                btb.setBookBean(bb);
                list.add(btb);
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;
    }

    public static int getNextDayReturning() throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from book_transaction where returning_date = to_date(sysdate)");
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                i++;
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }

    public static Vector IssuedBookReport() throws SQLException {
        Vector<Object> list = new Vector<Object>();
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from book_transaction bt left join book_details bd on bt.book_no=bd.book_number left join book b on bd.book_id=b.book_id where bd.BOOK_STATUS=1");

            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                BookTransBean btb = new BookTransBean();
                BookDetailsBean bdb = new BookDetailsBean();
                BookBean bb = new BookBean();
                btb.setBookTransactionId(rs.getInt(1));
                btb.setUserType(rs.getInt(2));
                btb.setEmployeeAddNo(rs.getString(3));
                btb.setIssueDate(rs.getDate(4));
                btb.setRetruningDate(rs.getDate(5));
                btb.setFine(rs.getInt(6));
                btb.setBookStatus(rs.getInt(7));
                btb.setBookNo(rs.getString(8));
                btb.setUserEmail(rs.getString(9));
                btb.setEntryDate(rs.getDate(10));
                bdb.setBookDetailsId(rs.getInt(11));
                bdb.setBookId(rs.getInt(12));
                bdb.setBookNumber(rs.getString(13));
                bdb.setBookStatus(rs.getInt(14));
                bdb.setBookStatusDescription(rs.getString(15));
                bdb.setEntryDate(rs.getDate(16));
                bb.setBookId(rs.getInt(17));
                bb.setBookCategoryId(rs.getInt(18));
                bb.setBookTypeId(rs.getInt(19));
                bb.setBookName(rs.getString(20));
                bb.setPublisher(rs.getString(21));
                bb.setAuthor(rs.getString(22));
                bb.setYear(rs.getString(23));
                bb.setQuantity(rs.getInt(24));
                bb.setPrice(rs.getFloat(25));
                bb.setFine(rs.getInt(26));
                bb.setGracePeriod(rs.getInt(27));
                bb.setEntryEmail(rs.getString(28));
                bb.setIsbn(rs.getString(29));
                bb.setBookDescription(rs.getString(30));
                bb.setEntryDate(rs.getDate(31));
                btb.setBookDetailsBean(bdb);
                btb.setBookBean(bb);
                list.add(btb);

            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;
    }

    public static Vector ReturnReports(String dFrom, String dTo) throws SQLException {
        Vector<Object> list = new Vector<Object>();
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from book_transaction bt left join book_details bd on bt.book_no=bd.book_number left join book b on bd.book_id=b.book_id where bd.BOOK_STATUS=0 and bt.entry_date between to_date(?) and  to_date(?)+1");
            pre.setString(1, dFrom);
            pre.setString(2, dTo);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                BookTransBean btb = new BookTransBean();
                BookDetailsBean bdb = new BookDetailsBean();
                BookBean bb = new BookBean();
                btb.setBookTransactionId(rs.getInt(1));
                btb.setUserType(rs.getInt(2));
                btb.setEmployeeAddNo(rs.getString(3));
                btb.setIssueDate(rs.getDate(4));
                btb.setRetruningDate(rs.getDate(5));
                btb.setFine(rs.getInt(6));
                btb.setBookStatus(rs.getInt(7));
                btb.setBookNo(rs.getString(8));
                btb.setUserEmail(rs.getString(9));
                btb.setEntryDate(rs.getDate(10));
                bdb.setBookDetailsId(rs.getInt(11));
                bdb.setBookId(rs.getInt(12));
                bdb.setBookNumber(rs.getString(13));
                bdb.setBookStatus(rs.getInt(14));
                bdb.setBookStatusDescription(rs.getString(15));
                bdb.setEntryDate(rs.getDate(16));
                bb.setBookId(rs.getInt(17));
                bb.setBookCategoryId(rs.getInt(18));
                bb.setBookTypeId(rs.getInt(19));
                bb.setBookName(rs.getString(20));
                bb.setPublisher(rs.getString(21));
                bb.setAuthor(rs.getString(22));
                bb.setYear(rs.getString(23));
                bb.setQuantity(rs.getInt(24));
                bb.setPrice(rs.getFloat(25));
                bb.setFine(rs.getInt(26));
                bb.setGracePeriod(rs.getInt(27));
                bb.setEntryEmail(rs.getString(28));
                bb.setIsbn(rs.getString(29));
                bb.setBookDescription(rs.getString(30));
                bb.setEntryDate(rs.getDate(31));
                btb.setBookDetailsBean(bdb);
                btb.setBookBean(bb);
                list.add(btb);
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close(); 
        }
        return list;
    }
    public static Vector DueBooksReports() throws SQLException {
        Vector<Object> list = new Vector<Object>();
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from book_transaction bt left join book_details bd on bt.book_no=bd.book_number left join book b on bd.book_id=b.book_id where to_date(bt.returning_date)<to_date(sysdate) and bt.book_status=1");

            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                BookTransBean btb = new BookTransBean();
                BookDetailsBean bdb = new BookDetailsBean();
                BookBean bb = new BookBean();
                btb.setBookTransactionId(rs.getInt(1));
                btb.setUserType(rs.getInt(2));
                btb.setEmployeeAddNo(rs.getString(3));
                btb.setIssueDate(rs.getDate(4));
                btb.setRetruningDate(rs.getDate(5));
                btb.setFine(rs.getInt(6));
                btb.setBookStatus(rs.getInt(7));
                btb.setBookNo(rs.getString(8));
                btb.setUserEmail(rs.getString(9));
                btb.setEntryDate(rs.getDate(10));
                bdb.setBookDetailsId(rs.getInt(11));
                bdb.setBookId(rs.getInt(12));
                bdb.setBookNumber(rs.getString(13));
                bdb.setBookStatus(rs.getInt(14));
                bdb.setBookStatusDescription(rs.getString(15));
                bdb.setEntryDate(rs.getDate(16));
                bb.setBookId(rs.getInt(17));
                bb.setBookCategoryId(rs.getInt(18));
                bb.setBookTypeId(rs.getInt(19));
                bb.setBookName(rs.getString(20));
                bb.setPublisher(rs.getString(21));
                bb.setAuthor(rs.getString(22));
                bb.setYear(rs.getString(23));
                bb.setQuantity(rs.getInt(24));
                bb.setPrice(rs.getFloat(25));
                bb.setFine(rs.getInt(26));
                bb.setGracePeriod(rs.getInt(27));
                bb.setEntryEmail(rs.getString(28));
                bb.setIsbn(rs.getString(29));
                bb.setBookDescription(rs.getString(30));
                bb.setEntryDate(rs.getDate(31));
                btb.setBookDetailsBean(bdb);
                btb.setBookBean(bb);
                list.add(btb);

            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;
    }
}
